package com.sinba.common.file.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PipedInputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class ExcelUtil {

	private static void createCell(
			HSSFRow row, HSSFCellStyle cellstyle, 
			short col, String val)
	{
		HSSFCell cell = row.createCell(col);
		cell.setCellStyle(cellstyle);
		HSSFRichTextString cellString = new HSSFRichTextString(val);
		cell.setCellValue(cellString);
	}
	
	public static List<String[]> readSheet(String sheetName,InputStream is)
		throws IOException
    {
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet= wb.getSheet(sheetName);
		List<String[]> data = new ArrayList<String[]>();
		int cols = sheet.getRow(0).getPhysicalNumberOfCells();
		for (int j=1; j<sheet.getPhysicalNumberOfRows(); j++) {
			String[] rowData = new String[cols];
			data.add(rowData);
			HSSFRow row = sheet.getRow(j);
			for (int i=0; i<cols; i++) {
				
				rowData[i] = row.getCell(i).getRichStringCellValue().getString();
			}
		}
		return data;
    }

	/**
	 * Export sheet
	 */
	public static void createSheet(String sheetName,
			String[] colNames,List<String[]> data,OutputStream os)
    {
		HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet=wb.createSheet(sheetName);
        HSSFCellStyle cellstyle = wb.createCellStyle();
		cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
		
        HSSFRow rowHead=sheet.createRow((short)0);
        sheet.createFreezePane(0,1);
        for (int i=0; i<colNames.length; i++) {
        	createCell(rowHead,cellstyle,(short)i,colNames[i]);
        }
        for(int j = 0;j<data.size();j++){
        	HSSFRow row = sheet.createRow((short)j+1);
        	String[] rowData = data.get(j);
        	for (int i=0; i<rowData.length; i++) {
        		createCell(row,cellstyle,(short)i,rowData[i]);
        	}
        }
        try{
        	 wb.write(os);
             os.flush();
             os.close();
        } catch(Exception e){
            e.printStackTrace();
        }
    }
	
	/**
	 * Export sheet
	 * @return 
	 */
	public static InputStream createSheetExcel(String sheetName,
			String[] colNames,List<String[]> data)
    {
		PipedInputStream pis = null ;
//		ByteArrayInputStream input=null;
		HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet=wb.createSheet(sheetName);
        HSSFCellStyle cellstyle = wb.createCellStyle();
		cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
		
        HSSFRow rowHead=sheet.createRow((short)0);
        sheet.createFreezePane(0,1);
        for (int i=0; i<colNames.length; i++) {
        	createCell(rowHead,cellstyle,(short)i,colNames[i]);
        }
        for(int j = 0;j<data.size();j++){
        	HSSFRow row = sheet.createRow((short)j+1);
        	String[] rowData = data.get(j);
        	for (int i=0; i<rowData.length; i++) {
        		createCell(row,cellstyle,(short)i,rowData[i]);
        	}
        }
        try{
//        	   PipedOutputStream  pos = new PipedOutputStream();
//        	   pis = new PipedInputStream(pos);   
//        	   wb.write(pos);
//        	
	        	ByteArrayOutputStream os = new ByteArrayOutputStream();
	        	wb.write(os);
//        	    input = new ByteArrayInputStream(wb.getBytes());
	        	
//	        	System.out.println("----------");
	        	return   new ByteArrayInputStream(os.toByteArray());

//        	 return input;

        	 
//        	return input;
        } catch(Exception e){
            e.printStackTrace();
        }
        return pis;
    }
	
	public static void testCreateSheetExcel() throws FileNotFoundException {
		//String filePath = "d:/my1.xls";
		String sheetName = "联系";
		String[] colNames = {"用户","密码","生日"};
		List<String[]> data = new ArrayList<String[]>();
		data.add(new String[]{"sinba","123","2006-3-8"});
		data.add(new String[]{"lily","123","2008-3-8"});
		createSheetExcel(sheetName, colNames,data);		
	}

	public static void testCreateSheet() throws FileNotFoundException {
		String filePath = "d:/my1.xls";
		String sheetName = "联系";
		String[] colNames = {"用户","密码","生日"};
		List<String[]> data = new ArrayList<String[]>();
		data.add(new String[]{"sinba","123","2006-3-8"});
		data.add(new String[]{"lily","123","2008-3-8"});
		createSheet(sheetName, colNames, data, new FileOutputStream(filePath));		
	}
	

	
	

	public static void testReadSheet() throws IOException {
		String filePath = "d:/my1.xls";
		String sheetName = "联系";		
		List<String[]> data = readSheet(sheetName, new FileInputStream(filePath));
		for (String[] rowData: data) {
			for (String cell: rowData) {
				System.out.print(cell+",");
			}
			System.out.println("");
		}
	}
	
	public static void main(String []args){
		try {
			ExcelUtil.testCreateSheetExcel();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
